package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.IModificarBOTDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.AtributoVO;
import co.gov.medellin.ssti.sub.model.vo.ModificarBOTVO;

public class ModificarBOTDAOImpl extends BaseDAOImpl implements
		IModificarBOTDAO {
	// consultas
	private static final String SELECT_ATRIBUTOS_SUBPROGRAMA = "SELECT CODIGO, NOMBRE, TIPO_DATO, TIPO_ATRIBUTO "
			+ "FROM TCS_BEN_SUBP_ATR_BENEFICIARIO WHERE SBPR_CODIGO = ? "
			+ "AND VIGENCIA = 'A' AND CAMPO_A2 IS NULL ";
	private static final String SELECT_SEDE_OTORGAMIENTO = "SELECT CODSEDE FROM VCS_BENEFICIOS_OTORGADOS "
			+ "WHERE CODBOT = ? ";
	private static final String SELECT_BENEFICIARIO_VALOR_NUM_ATR = "SELECT VALOR_NUM_ATR "
			+ " valor FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE BEN_CODIGO = ? AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIARIO_VALOR_FEC_ATR = "SELECT TO_CHAR(VALOR_FEC_ATR,'DD/MM/RRRR') "
			+ " valor, codigo FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE BEN_CODIGO = ? AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIARIO_VALOR_CAR_ATR = "SELECT VALOR_CAR_ATR "
			+ " valor, codigo FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE BEN_CODIGO = ? AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIO_VALOR_NUM_ATR = "SELECT VALOR_NUM_ATR "
			+ " valor, codigo FROM TCS_BEN_BENEFICIO_ATRIBUTO "
			+ "WHERE BOT_CODIGO = ? AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIO_VALOR_FEC_ATR = "SELECT TO_CHAR(VALOR_FEC_ATR,'DD/MM/RRRR') "
			+ " valor, codigo FROM TCS_BEN_BENEFICIO_ATRIBUTO "
			+ "WHERE BOT_CODIGO = ? AND SAB_CODIGO = ? ";
	private static final String SELECT_BENEFICIO_VALOR_CAR_ATR = "SELECT VALOR_CAR_ATR "
			+ " valor, codigo FROM TCS_BEN_BENEFICIO_ATRIBUTO "
			+ "WHERE BOT_CODIGO = ? AND SAB_CODIGO = ? ";

	public ModificarBOTDAOImpl(Connection con) {
		super(con);
	}

	public Collection<AtributoVO> findAtributosSubpro(String codeSubpro)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		int codeSub = Integer.parseInt(codeSubpro.trim());
		List<AtributoVO> lAtributos = new LinkedList<AtributoVO>();
		try {
			pstmt = conn.prepareStatement(SELECT_ATRIBUTOS_SUBPROGRAMA);
			pstmt.setInt(1, codeSub);
			rst = pstmt.executeQuery();
			AtributoVO objatri;
			while (rst.next()) {
				objatri = new AtributoVO();
				objatri.setCodatributo(rst.getString("CODIGO"));
				objatri.setNombre(rst.getString("NOMBRE"));
				objatri.setTipodato(rst.getString("TIPO_DATO"));
				objatri.setTipoatributo(rst.getString("TIPO_ATRIBUTO"));
				lAtributos.add(objatri);
				objatri = null;
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error GetAtributosSubpro :", e);
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lAtributos;
	}

	public String findSedeOtorgamientoByCodBot(String codeBot)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String sede = "";
		try {
			pstmt = conn.prepareStatement(SELECT_SEDE_OTORGAMIENTO);
			pstmt.setString(1, codeBot);
			rst = pstmt.executeQuery();

			if (rst.next()) {
				return rst.getString("CODSEDE");
			}
		} catch (Exception e) {
			SUBException.logDebugError("Error GetSedeOtorgamiento.", e);
		} finally {
			close(rst, pstmt);
		}
		return sede;
	}

	public String findBeneficiarioAtri(String tipodato, String codebene,
			String codeAtri) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String consulta;
		String strValor = null;
		if (tipodato.equals("N")) {
			consulta = SELECT_BENEFICIARIO_VALOR_NUM_ATR;
		} else if (tipodato.equals("F")) {
			consulta = SELECT_BENEFICIARIO_VALOR_FEC_ATR;
		} else {
			consulta = SELECT_BENEFICIARIO_VALOR_CAR_ATR;
		}

		int codbeneficiario = Integer.parseInt(codebene.trim());
		int codatri = Integer.parseInt(codeAtri.trim());

		try {
			pstmt = conn.prepareStatement(consulta);
			pstmt.setInt(1, codbeneficiario);
			pstmt.setInt(2, codatri);
			rst = pstmt.executeQuery();

			if (rst.next()) {
				strValor = rst.getString("valor");
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error findBeneficiarioAtri: ", e);
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return strValor;
	}

	public ModificarBOTVO findBeneficioAtri(String tipoDato,
			String codeBeneOtor, String codeAtri) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ModificarBOTVO modificarBOTVO;
		String consulta;
		if (tipoDato.equals("N")) {
			consulta = SELECT_BENEFICIO_VALOR_NUM_ATR;
		} else if (tipoDato.equals("F")) {
			consulta = SELECT_BENEFICIO_VALOR_FEC_ATR;
		} else {
			consulta = SELECT_BENEFICIO_VALOR_CAR_ATR;
		}

		int codbeneotor = Integer.parseInt(codeBeneOtor.trim());
		int codatri = Integer.parseInt(codeAtri.trim());

		modificarBOTVO = new ModificarBOTVO();
		try {
			pstmt = conn.prepareStatement(consulta);
			pstmt.setInt(1, codbeneotor);
			pstmt.setInt(2, codatri);
			rst = pstmt.executeQuery();
			// solo trae un reg. creo.
			while (rst.next()) {
				modificarBOTVO.setValorInicial(rst.getString("valor"));
				modificarBOTVO.setCodigo(new Long(rst.getLong("codigo")));
			}
		} catch (SQLException e) {
			SUBException.logDebugError("Error Getbeneficioatri: ", e);
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return modificarBOTVO;
	}
}
